package org.zachary.commonservice.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.zachary.commonservice.entity.*;

import java.util.List;
import java.util.Map;

@Mapper
public interface CreditContractRecordMapper {




    @Select("""
    SELECT 
        user_id, 
        record_type, 
        amount, 
        status
    FROM credit_contract_record
    ORDER BY finish_time DESC
    LIMIT 20
""")
    List<CreditRecordSimpleDTO> selectLatestRecords();


    @Select("""
        SELECT '酒店预定' AS category, IFNULL(SUM(amount), 0) AS totalAmount
        FROM credit_contract_record
        WHERE description LIKE '%酒店%'
          AND status = '已完成'
        UNION
        SELECT '舒心就医', IFNULL(SUM(amount), 0)
        FROM credit_contract_record
        WHERE description LIKE '%舒心就医%'
          AND status = '已完成'
        UNION
        SELECT '便捷停车', IFNULL(SUM(amount), 0)
        FROM credit_contract_record
        WHERE description LIKE '%便捷停车%'
          AND status = '已完成'
        UNION
        SELECT '房屋租赁', IFNULL(SUM(amount), 0)
        FROM credit_contract_record
        WHERE description LIKE '%房屋租赁%'
          AND status = '已完成'
        UNION
        SELECT '信用购物', IFNULL(SUM(amount), 0)
        FROM credit_contract_record
        WHERE description LIKE '%信用购物%'
          AND status = '已完成'
    """)
    List<CreditCategoryStatsDTO> getCreditCategoryStats();

    @Select("""
    SELECT '酒店预定' AS category, IFNULL(SUM(c.amount), 0) AS totalAmount
    FROM credit_contract_record c
    JOIN user u ON c.user_id = u.id
    WHERE c.description LIKE '%酒店%'
      AND c.status = '已完成'
      AND (#{county} = '全部' OR u.county = #{county})
    UNION
    SELECT '舒心就医', IFNULL(SUM(c.amount), 0)
    FROM credit_contract_record c
    JOIN user u ON c.user_id = u.id
    WHERE c.description LIKE '%舒心就医%'
      AND c.status = '已完成'
      AND (#{county} = '全部' OR u.county = #{county})
    UNION
    SELECT '便捷停车', IFNULL(SUM(c.amount), 0)
    FROM credit_contract_record c
    JOIN user u ON c.user_id = u.id
    WHERE c.description LIKE '%便捷停车%'
      AND c.status = '已完成'
      AND (#{county} = '全部' OR u.county = #{county})
    UNION
    SELECT '房屋租赁', IFNULL(SUM(c.amount), 0)
    FROM credit_contract_record c
    JOIN user u ON c.user_id = u.id
    WHERE c.description LIKE '%房屋租赁%'
      AND c.status = '已完成'
      AND (#{county} = '全部' OR u.county = #{county})
    UNION
    SELECT '信用购物', IFNULL(SUM(c.amount), 0)
    FROM credit_contract_record c
    JOIN user u ON c.user_id = u.id
    WHERE c.description LIKE '%信用购物%'
      AND c.status = '已完成'
      AND (#{county} = '全部' OR u.county = #{county})
""")
    List<CreditCategoryStatsDTO> getCreditCategoryStatsByCounty(@Param("county") String county);



    Integer countAllUsers();
    Integer countAllUsing();

    @Select("""
    SELECT
        COUNT(*) AS totalCount,
        SUM(CASE
                WHEN description LIKE '%逾期%' OR description LIKE '%逾约%' THEN 1
                ELSE 0
            END) AS breachCount
    FROM credit_contract_record
    """)
    CreditContractRateDTO getContractRateByDescription();



    @Select("""
        SELECT u.county, COALESCE(AVG(uc.annual_income), 0) AS total_income
        FROM user u
        LEFT JOIN user_credit uc ON u.id = uc.id
        WHERE u.county IS NOT NULL
        GROUP BY u.county
        ORDER BY FIELD(u.county, '上城区','下城区','拱墅区','西湖区','滨江区','钱塘区','萧山区','余杭区','富阳区','临安区')
    """)
    List<CountyIncomeDTO> getIncomeByCounty();

    @Select("""
    SELECT 
        u.county AS county,
        uc.credit_score AS creditScore
    FROM user u
    JOIN user_credit uc ON u.id = uc.id
    WHERE u.county IN (
        '上城区', '拱墅区', '西湖区', '滨江区', '萧山区',
        '余杭区', '临平区', '钱塘区', '富阳区', '临安区'
    ) AND uc.credit_score IS NOT NULL
""")
    List<CountyScoreDTO> findAllScoreWithCounty();

    @Select("""
        SELECT u.nick_name, c.dimension_score, c.dimension_type
        FROM credit_score_dimension c
        JOIN user u ON c.user_id = u.id
        JOIN (
          SELECT dimension_type, MAX(dimension_score) AS max_score
          FROM credit_score_dimension
          GROUP BY dimension_type
        ) AS max_scores
        ON c.dimension_type = max_scores.dimension_type
           AND c.dimension_score = max_scores.max_score
        """)
    List<DimensionTopUserDTO> selectTopUsersByDimension();

}
